﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;

namespace Kakeibo.Model
{
    class KakeiboDatabase
    {
        private string dbFileName_;
        private SQLiteConnection connection_ = null;
        private Dictionary<string, int> categoryHash;

        public KakeiboDatabase() {
        }


        public bool Open(string dbFileName) {
            dbFileName_ = dbFileName;
            connection_ = new SQLiteConnection("Data Source=" + dbFileName_);
            connection_.Open();
            
            if(false){ // drop tables (test)
                if (HasTable("category"))  ExecuteNonQuery("drop table category");
                if (HasTable("kakeibo"))  ExecuteNonQuery("drop table kakeibo");
                if (HasTable("summary"))  ExecuteNonQuery("drop table summary");
            }

            categoryHash = new Dictionary<string, int>();
            if(HasTable("category")){
                InitCategoryHash();
            }

            CreateTables();

            return true;
        }


        public bool Close() {
            connection_.Close();
            return true;
        }


        public bool HasTable(String tableName){
            bool hasTable;
            using (var cmd = connection_.CreateCommand()){
                cmd.CommandText = "select * from sqlite_master where type='table' and name='" + tableName + "'";
                var reader = cmd.ExecuteReader();
                hasTable = reader.HasRows;
                reader.Close();
            }
            return hasTable;
        }

        public List<object[]> ReadData(string sql) {
            var result = new List<object[]>();
            using (var cmd = connection_.CreateCommand()) {
                cmd.CommandText = sql;
                var reader = cmd.ExecuteReader();

                while (reader.Read()) {
                    var row = new object[reader.FieldCount];
                    for (int i = 0; i < reader.FieldCount; i++) {
                        row[i] = reader[i];
                    }
                    result.Add(row);
                }

                reader.Close();
            }

            return result;
        }

        public void AddNewEntry(string debtorName, string debtorSubName, string creditorName, string creditorSubName,
                                int amount, DateTime transactionDate) {
            int debtorId, debtorSubId, creditorId, creditorSubId;

            debtorId = categoryHash[debtorName];
            debtorSubId = categoryHash[debtorSubName];
            creditorId = categoryHash[creditorName];
            creditorSubId = categoryHash[creditorSubName];

            AddNewEntry(debtorId, debtorSubId, creditorId, creditorSubId, amount, transactionDate);
        }


        public void AddNewEntry(int debtorId, int debtorSubId, int creditorId, int creditorSubId,
                                int amount, DateTime transactionDate) {
            { // update kakeibo
                DateTime updateDate = DateTime.Now;
                string sql = String.Format(
                        @"insert into kakeibo(
                            debtor_id,
                            debtor_sub_id,
                            creditor_id,
                            creditor_sub_id,
                            amount,
                            transaction_year,
                            transaction_month,
                            transaction_day,
                            update_year,
                            update_month,
                            update_day
                        )
                        values(
                            {0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10}
                        )",
                        debtorId,
                        debtorSubId,
                        creditorId,
                        creditorSubId,
                        amount,
                        transactionDate.Year,
                        transactionDate.Month,
                        transactionDate.Day,
                        updateDate.Year,
                        updateDate.Month,
                        updateDate.Day
                    );
                ExecuteNonQuery(sql);
            }
            {   // update summary
                if (debtorId != 0) {
                    UpdateSummary(transactionDate.Year, transactionDate.Month, debtorId, amount);
                    UpdateSummary(transactionDate.Year, 0, debtorId, amount);
                }
                if (creditorId != 0) {
                    UpdateSummary(transactionDate.Year, transactionDate.Month, creditorId, -amount);
                    UpdateSummary(transactionDate.Year, 0, creditorId, -amount);
                }
                if (debtorSubId != 0) {
                    UpdateSummary(transactionDate.Year, transactionDate.Month, debtorSubId, amount);
                    UpdateSummary(transactionDate.Year, 0, debtorSubId, amount);
                }
                if (creditorSubId != 0) {
                    UpdateSummary(transactionDate.Year, transactionDate.Month, creditorSubId, -amount);
                    UpdateSummary(transactionDate.Year, 0, creditorSubId, -amount);
                }
            }
        }


        public void AddNewCategory(int id, int parentId, string name, bool isCreditor=false, bool isAccount=false){
            ExecuteNonQuery(
                String.Format(
                    @"insert into category values({0},{1},'{2}',{3},{4})",
                    id,
                    parentId,
                    name,
                    isCreditor ? 1 : 0,
                    isAccount ? 1 : 0
                )
            );
            categoryHash.Add(name, id);
        }


        public void ClearEntry() {
            ExecuteNonQuery("delete from kakeibo");
            ExecuteNonQuery("delete from summary");
        }


        public void ClearCategory() {
            ExecuteNonQuery("delete from category");
            categoryHash.Clear();
        }

        public int GetCategoryId(string name) {
            return categoryHash[name];
        }


        private void UpdateSummary(int year, int month, int categoryId, int amount) {
            bool existRow;
            using (var cmd = connection_.CreateCommand()){
                cmd.CommandText = String.Format("select * from summary where year={0} and month={1} and category_id={2}", year, month, categoryId);
                var reader = cmd.ExecuteReader();
                existRow = reader.HasRows;
                reader.Close();
            }
            if (existRow) {
                ExecuteNonQuery(String.Format(
                    @"update summary
                        set total_amount = total_amount {0} {1}
                        where year = {2} and month = {3} and category_id = {4}
                    ",
                    amount > 0 ? '+' : '-',
                    Math.Abs(amount),
                    year,
                    month,
                    categoryId
                ));
            }
            else {
                ExecuteNonQuery(String.Format(
                    @"insert into summary (year, month, category_id, total_amount) values ({0},{1},{2},{3})",
                    year, month, categoryId, amount
                    ));
            }
        }
        
        private void ExecuteNonQuery(string sql) {
            using (var cmd = connection_.CreateCommand()) {
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }
        }


        private void InitCategoryHash() {
            var rows = ReadData("select id, name from category");

            foreach (var col in rows) {
                categoryHash[(string)col[1]] = (int)(long)col[0];
            }
        }


        private void CreateTables() {
            if (!HasTable("category")) {
                ExecuteNonQuery(
                    @"create table category(
                        id integer primary key,
                        parent_category_id integer,
                        name text,
                        is_creditor integer,
                        is_account integer
                    )"
                );
                InitCategory();
            }
            if (!HasTable("summary")) {
                ExecuteNonQuery(
                    @"create table summary(
                        id integer primary key,
                        year integer,
                        month integer,
                        category_id integer,
                        total_amount integer,
                        unique(year, month, category_id)
                    )"
                );
            }
            if (!HasTable("kakeibo")) {
                ExecuteNonQuery(
                    @"create table kakeibo(
                        id integer primary key,
                        debtor_id integer,
                        debtor_sub_id integer,
                        creditor_id integer,
                        creditor_sub_id integer,
                        amount integer,
                        transaction_year integer,
                        transaction_month integer,
                        transaction_day integer,
                        update_year integer,
                        update_month integer,
                        update_day integer
                    )"
                );
            }
        }


        private void InitCategory() {
            AddNewCategory(0, 0, "");
            AddNewCategory(1, 0, "食費");
            AddNewCategory(2, 0, "生活用品");
            AddNewCategory(3, 0, "通信・光熱費");
            AddNewCategory(4, 0, "美容・ファッション");
            AddNewCategory(5, 0, "交通費");
            AddNewCategory(6, 0, "育児・教育費");
            AddNewCategory(7, 0, "趣味・娯楽費");
            AddNewCategory(8, 0, "交際費");
            AddNewCategory(9, 0, "医療費");
            AddNewCategory(10, 0, "税金・保険");
            AddNewCategory(11, 0, "特別出費");
            AddNewCategory(12, 0, "その他");
            AddNewCategory(13, 0, "口座", isAccount:true);
            AddNewCategory(14, 0, "収入", isCreditor:true);

            AddNewCategory(101, 1, "食材");
            AddNewCategory(102, 1, "外食");
            AddNewCategory(103, 1, "菓子・飲料");

            AddNewCategory(201, 2, "消耗品");
            AddNewCategory(202, 2, "家具");
            AddNewCategory(203, 2, "電化製品");

            AddNewCategory(301, 3, "インターネット料金");
            AddNewCategory(302, 3, "携帯電話代");
            AddNewCategory(303, 3, "電気料金");
            AddNewCategory(304, 3, "ガス料金");
            AddNewCategory(305, 3, "水道料金");

            AddNewCategory(401, 4, "服");
            AddNewCategory(402, 4, "美容院");
            AddNewCategory(403, 4, "化粧品");

            AddNewCategory(501, 5, "ガソリン代");
            AddNewCategory(502, 5, "高速・有料道路");
            AddNewCategory(503, 5, "公共交通機関");

            AddNewCategory(601, 6, "授業料");
            AddNewCategory(602, 6, "教科書・参考書");

            AddNewCategory(701, 7, "書籍");
            AddNewCategory(702, 7, "CD・レンタルビデオ");
            AddNewCategory(703, 7, "レジャー・旅行");

            AddNewCategory(801, 8, "交遊費");
            AddNewCategory(802, 8, "贈答品");
            AddNewCategory(803, 8, "冠婚葬祭");

            AddNewCategory(901, 9, "治療費");
            AddNewCategory(902, 9, "医薬品");

            AddNewCategory(1001, 10, "税金");
            AddNewCategory(1002, 10, "保険");

            AddNewCategory(1101, 11, "旅行");
            AddNewCategory(1102, 11, "設備投資");

            AddNewCategory(1301, 13, "財布(夫)", isAccount:true);
            AddNewCategory(1302, 13, "財布(妻)", isAccount:true);
            AddNewCategory(1303, 13, "生活費用口座", isAccount:true);
            AddNewCategory(1304, 13, "特別出費用口座", isAccount:true);
            AddNewCategory(1305, 13, "貯蓄用口座", isAccount:true);

            AddNewCategory(1401, 14, "給与", isCreditor: true);
            AddNewCategory(1402, 14, "賞与", isCreditor: true);
        }
    }
}
